﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsMatReturnDataAccess
    {
        public static DataTable SelectList(string strBillID, string strFrom, string strTo, string strWorkOrderNo, string strMaterialName, int IMatInStockCount, out int icurPageIndex, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_MatReturnDetail A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and A.BillID LIKE @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and A.WorkOrderNo LIKE @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and A.MaterialName LIKE @MaterialName";
                    dpc.AddWithValue("@MaterialName", "%" + strMaterialName + "%");
                }


                strcmd += " ORDER BY A.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, PageSize, dpc, IMatInStockCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectMat(string MatInstockID, string strFrom, string strTo, string MaterialID, string MaterialName, string MatReturnID, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select ms.BillID as MatInStockID,ms.BillDate,ms.SupplierID,mi.BillDetailID as MatInStockDetailID, 
		mi.WorkOrderNo,mi.MaterialID,mi.Nums as InStockNum,mi.MMNumber,
		m.MaterialName,m.Specifications,m.Unit,m.MMNumber as SingleMMNumber,
		ISNULL(mi.Nums,0)-ISNULL(c.ReturnNums,0) as LeftNums
from BS_MatInStock ms
left join BS_MatInStockDetail mi on mi.BillID=ms.BillID
left join MD_Material m on m.MaterialID=mi.MaterialID
left join (select MatInStockDetailID,SUM(ISNULL(Nums,0)) as ReturnNums 
			from BS_MatReturnDetail
			where BillID<>@MatReturnID
			group by MatInStockDetailID) c on c.MatInStockDetailID=mi.BillDetailID
WHERE ISNULL(mi.Nums,0)-ISNULL(c.ReturnNums,0)>0";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@MatReturnID", MatReturnID);
                if (!string.IsNullOrEmpty(MatInstockID))
                {
                    strcmd += " and ms.BillID LIKE @MatInstockID";
                    dpc.AddWithValue("@MatInstockID", "%" + MatInstockID + "%");
                }
                if (!string.IsNullOrEmpty(MaterialID))
                {
                    strcmd += " and mi.MaterialID LIKE @MaterialID";
                    dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
                }
                if (!string.IsNullOrEmpty(MaterialName))
                {
                    strcmd += " and m.MaterialName LIKE @strMaterialName";
                    dpc.AddWithValue("@strMaterialName", "%" + MaterialName + "%");
                }
                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and ms.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(MaterialName))
                {
                    strcmd += " and ms.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                strcmd += " ORDER BY ms.BillDate DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable GetDataByID(string id)
        {
            try
            {
                #region SQL Server
                string strcmd = @" select mr.*,p.ParameterName as ReturnReasonName,mrd.MatInStockDetailID,mrd.MatInStockID,
		                            mrd.BillDetailID,mrd.WorkOrderNo,mrd.MaterialID,mrd.Nums,mrd.MMNumber,
		                            m.MaterialName,m.Specifications,m.Unit,m.MeterWeight,mid.Nums as InStockNum,
		                            m.MMNumber as SingleMMNumber,ISNULL(mid.Nums,0)-ISNULL(c.ReturnNums,0) as LeftNums,c.ReturnNums
                                 from BS_MatReturn mr
                                left join BS_MatReturnDetail mrd on mrd.BillID=mr.BillID
                                left join BS_MatInStockDetail mid on mid.BillDetailID=mrd.MatInStockDetailID
                                left join MD_Material m on m.MaterialID=mrd.MaterialID
                                left join SYS_Parameter p on p.ParameterCode=mr.ReturnReason and p.ParameterTypeID='ReturnReason'
                                left join (select MatInStockDetailID,SUM(ISNULL(Nums,0)) as ReturnNums 
			                                from BS_MatReturnDetail
			                                where BillID<>@BillID
			                                group by MatInStockDetailID) c on c.MatInStockDetailID=mid.BillDetailID
                                where mr.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@BillID", id);

                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
